Shareware Grab Bag

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Grab Bag / Shareware Grab Bag.iso / 006 / expcalc2.arc / CALC1.DOC next >

Wrap

Text File | 1986-09-23 | 97.8 KB | 3,565 lines

- _________________________________________________________________ ExpressCalc User's Guide by Bill Willis and Ralph Osness E X P R E S S W A R E P.O. Box 230 Redmond, WA 98073 Copyright 1986 _________________________________________________________________ - _________________________________________________________________ Changes are periodically made to the information contained herein. These changes will be incorporated into future editions of this publication. A User Response Form is provided at the back of this publication. If this form has been removed, please address your comments to: E X P R E S S W A R E P.O. Box 230 Redmond, WA 98073 Proprietary Notice ExpressCalc is a proprietary product written by Bill Willis and Ralph Osness, and is published by Expressware, Redmond, Washington. All rights reserved except those expressly granted to the user by this document. _________________________________________________________________ - Contents ____________________________________________________________________ | 1 Introduction | | 3 System Requirements | | 4 Getting Started | | 6 A Brief Tutorial | | 11 General Information | | 16 Keyboard Conventions | | 23 ALPHABETIC REFERENCE | 25 ABS Function | 26 ARRANGE Command (/A) | 31 ATN Function | 32 AVERAGE Function | 33 BLANK Command (/B) | 35 CONFIGURE Command (/C) | 42 COS Function | 43 COUNT Function | 44 CPI Function | 46 DELETE Command (/D) | 48 EDIT Command (/E) | 50 EXP Function | 51 FIX Function | 52 FORMAT Command (/F) | 60 GLOBAL Command (/G) | 64 IF Function | 69 INSERT Command (/I) | 71 INT Function | 72 KEYWORD Function | 75 LOAD Command (/L) | 79 LOG Function | 80 LOOKUP Function | 84 LPI Function | 85 MAX Function - Contents | 86 MIN Function | 87 NOLF Function | 89 NOPRINT Function | 92 NPV Function | 94 PAGE Function | 97 PAYMENT Function | 99 PERIODS Function | 101 PRINCIPAL Function | 103 PRINT Command (/P) | 109 QUIT Command (/Q) | 110 RANDOM Function | 112 RATE Function | 114 REPLICATE Command (/R) | 119 ROUND Function | 120 SAVE Command (/S) | 125 SGN Function | 125 SIN Function | 126 SQR Function | 127 STDEV Function | 128 SUM Function | 129 TAN Function | 130 TITLE Command (/T) | 132 XTERNAL Command (/X) | 138 ZAP Command (/Z) | | 139 Customizing CALC | | 154 CALC File Format | | 161 Version Numbers & Major Enhancements | | 164 The Files On The CALC Disks | | 165 Some Frequently Asked Questions | | 166 Distribution Notice | | 167 User Response Form | | 171 Order Form | | 173 Index - INTRODUCTION ____________________________________________________________________ INTRODUCTION ExpressCalc is an easy-to-use "Visible Spreadsheet" program. If you work with numbers, at home, on the job or at school, CALC is for you. Whether the task is simple or complex, CALC can help. By using its powerful commands, reports can be produced in minutes that would take hours to do manually, or days to write in BASIC. Here are some examples of how people are using CALC: . Computing interest and principal on loans, mortgages, savings accounts. . Preparing payroll journals, sales journals, accounts receivable journals. . Doing income tax computations, computing depreciation, accumulating income and expenses. . Preparing business forecasts, P&L's, ten-year projections, etc. and then playing "what if..." to see the impact of changing one or more numbers. . Analyzing investments, balancing checkbooks, keeping budgets. . Preparing homework assignments for classes in bookkeeping, accounting, math, etc. Spreadsheets created with CALC can be displayed and altered on the screen, printed out, saved on disk and reloaded for further changes or review. If you create a really wide spreadsheet that won't fit on one page, no problem. CALC will print two pages that can be attached to make one wide spreadsheet. And spreadsheets are not limited to the size of your computer screen. CALC will scroll up, down and sideways to give you a 1 - INTRODUCTION spreadsheet up to 64 columns wide and 256 lines long. For large spreadsheets, columns or rows can be increased with the "Configure" command. Numbers and words can be placed anywhere on the spreadsheet. The width of each column of your spreadsheet can be any size from 1 character to 75 characters wide, or it can be "zero" characters wide, which means it exists and contains data, but is hidden from view. A number can have from one to twelve decimals, or no decimals at all. Spreadsheets can link to other spreadsheets to extract data, providing a "3-dimensional" capability. And if you are a File Express or PC-File database user, CALC can reach into your databases and extract data or add up numbers. And there is no limit to the number of databases that can be accessed by a single spreadsheet. These are just a few of the features that CALC provides. This manual describes all the features of CALC in detail. It is recommended that the manual be read completely before using CALC, but you will find that it is so easy to use, many of its features can be mastered even without the documentation. For that reason, this manual has been organized as a reference document rather than a tutorial. There is a tutorial section at the beginning, titled "A Brief Tutorial", but the rest of the manual is designed for quickly finding information about specific commands and functions. 2 - SYSTEM REQUIREMENTS ____________________________________________________________________ SYSTEM REQUIREMENTS CALC runs on the IBM PC or compatible systems with this minimum configuration: . 256K computer memory. . An MSDOS or PCDOS operating system version 2.0 or later. . A monochrome or color monitor with either 40 or 80 column display. A color graphics board is not necessary, but if you have one, CALC will format its screens in your choice of colors. If your computer has less than 25 lines, CALC can be configured for a shorter screen (8 lines minimum). . Two double sided diskette drives. CALC is also compatible with hard disks and electronic disks. . A printer is optional. CALC will run on most printers. 3 - GETTING STARTED ____________________________________________________________________ GETTING STARTED The following information is provided to get you up and running with CALC as quickly as possible. We recognize that most people will try running a program before they read the User's Guide. This section will give you enough information to make a backup copy of CALC and to get the program started. If you have any further questions, refer to the manual. 1. Turn on your computer and load the DOS operating system. The CALC diskette does not have DOS on it, so use a diskette that does. 2. FORMAT two blank diskettes using the DOS FORMAT command. (See your DOS manual if needed.) 3. Copy COMMAND.COM from your DOS diskette onto one of the blank diskettes and label it "Program Disk". Copy all the program files from the original CALC Program Disk onto this diskette. 4. Label the second formatted diskette "Supplemental Disk" and copy all the files from the CALC Supplemental Disk onto it. This diskette can also be used as a data disk for storing spreadsheet files. 5. At this point, most users will simply type: CALC and press ENTER, which will load and run the program. However, there are some special cases when CALC cannot automatically configure itself, and an extra code must be entered, as follows: Computers with a forty-column display: CALC 40 4 - GETTING STARTED Computers with a color board, but a monochrome monitor: CALC BW Computers with a color board, but a monochrome monitor, and a forty-column display: CALC BW40 Non-IBM computers which do not have IBM-compatible video memory mapping (such as the Tandy 2000): CALC SL 6. The CALC banner screen will appear for a few seconds as the program is loaded into the computer. 7. Check the screen. If it doesn't look right, or if it doesn't show A1 > in the lower left corner, read the Configure (/C) section before proceeding. Otherwise, CALC is ready to use! CALC may also be used on a hard disk. It is suggested that you create a separate subdirectory, then copy the programs and data from both diskettes to it. 5 - A BRIEF TUTORIAL ____________________________________________________________________ A BRIEF TUTORIAL Now that you have CALC loaded in your computer, note that across the top of the screen are letters: A, B, C, etc. These letters identify the columns of the spreadsheet. Down the left side are numbers. These numbers identify each row of the spreadsheet. Each coordinate in this grid is called a "cell". For example, the cell where the cursor is currently located is called "A1"; to its right is "B1"; below it is "A2"; at the end of the spreadsheet (not currently on the screen) is "BL256". Press the arrow keys and you will find that you can move the large cursor (called the "cell cursor") around on the screen. When moving left and right, it moves one column at a time. Up and down it moves one row at a time. In the lower left corner of the screen is an indicator called the "cursor coordinate", which shows the cell currently occupied. As the cursor is moved about the screen, this cursor coordinate will rapidly change to the new cell address. Press the right-arrow several times and you will see the screen "scroll sideways". In other words, column A will disappear on the left, and all the other columns will shift to the left, making room for a new column on the right. If you keep pressing the right-arrow key, eventually you will come to column Z. Then the columns will be labelled AA, AB, AC, etc. When the cursor reaches column BL it will no longer move. You have reached the edge of the spreadsheet. Using the down arrow key you can make the screen "scroll up" until the last row of the screen, row 256, is displayed. When pressing the arrow keys to scroll, if you hold down the arrow key, causing it to rapidly repeat, you will notice something a bit different. The cursor coordinate in the lower left corner of the screen starts advancing rapidly, but the screen is not redisplayed until you let up on the arrow key. This allows you to scroll quickly to any position in the spreadsheet without waiting for the screen to be refreshed. 6 - A BRIEF TUTORIAL Now let's try putting some data on the spreadsheet. Move the cursor to coordinate B4, and type 12345 then press the ENTER key. As you type the number, you will notice that it is displayed on the bottom line of the screen. This line is called the "entry line". When you press ENTER, the number on the entry line disappears, and is redisplayed up in the spreadsheet at coordinate B4. But it is slightly different. Instead of being simply the 12345 you typed, it is 12,345.00 and it is shifted to the right of the column. The number is displayed this way because of certain "defaults" in CALC for displaying numbers. Unless otherwise specified, all numbers will have two decimal places, will have commas between the thousands, and will be "right-justified" in the column. Of course it is possible to change these defaults, for specific cells or for the whole screen, and this will be discussed later in the "Format Command" section. Now press the up-arrow and move the cell cursor to B2. Type the following: This is a test of CALC. and press the ENTER key. As you are typing, if you make an error, you can use the backspace key to move the cursor back, or the tab key to move forward across the entry line, then make typing corrections. After pressing ENTER, look at cell B2. It will now contain the text you typed, and the text will overlap into cells C2 and D2. Text is allowed to overlap cells in CALC, provided the cells to the right have nothing in them. Now move the cursor to B6 and type: B4+B4 then press the ENTER key. Cell B6 should display the number 24,690.00 which is the sum of 12345 plus 12345. Let's examine what happened. By moving the cursor to B6, you told CALC to put the result of the formula into B6. The formula B4+B4 told CALC to get the number at B4, add it to itself, and put the result in B6. The formula could have been simpler or more complex and CALC would have immediately gathered up all the numbers needed, computed the new value, and displayed the result at B6. 7 - A BRIEF TUTORIAL Looking at the screen, you have now entered all three types of data that can go on a CALC spreadsheet: At B2 you entered the "text" This is a test of CALC. At B4 you entered the "value" 12345 At B6 you entered the "formula" B4+B4 Any cell on the spreadsheet can contain text, a value or a formula. Now move the cell cursor to each of the three cells with data in them. As the cell cursor moves into a cell, notice that the "cell contents" are displayed at the bottom of the screen on the "message line". The cell contents are the actual letters or numbers you typed when entering data into the cell. As a final exercise in this tutorial, type /Z Y to clear the worksheet (a complete explanation of the Zap (/Z) command appears later in the manual). Move the cursor to each cell listed below, and enter the data exactly as shown. If you get an error message on a line, press the ESC key to clear the entry line, then type it again. All lines should go in as shown without errors. At Coordinate: Enter this data: B2 Compute Annual Interest B4 Rate: C4 5.5 B6 Year C6 Balance D6 Interest B7 "1983 C7 5000 D7 C7*C4/100 B8 "1984 C8 C7+D7 D8 C8*C4/100 B9 "1985 C9 C8+D8 D9 C9*C4/100 B11 Totals C11 C9+D9 D11 SUM(D7:D9) 8 - A BRIEF TUTORIAL When the preceding data has been entered, your spreadsheet screen should look like this: A B C D 1 2 Compute Annual Interest 3 4 Rate: 5.50 5 6 Year Balance Interest 7 1983 5,000.00 275.00 8 1984 5,275.00 290.13 9 1985 5,565.13 306.08 10 11 Totals 5,871.21 871.21 This simple spreadsheet computes the interest on a savings account at a specified annual interest rate. Since this is a sample exercise, the spreadsheet has been kept small, but later you will learn how to use the "Replicate" command to extend a spreadsheet like this one to fifty years or more, in a matter of seconds. The "SUM" function in cell D11 is simply adding up the numbers in D7 through D9, so you can see the accumulated interest for three years. Now let's try some "what if" experiments. What if the interest rate were seven percent instead of 5.5? To see the result, simply move to cell C4, type 7 and press ENTER. All the figures on the screen are immediately updated with the new interest computation. Or what if the interest were 18%? Try it. What if there were only $850.25 instead of $5000? Move the cursor to C7 and enter 850.25 and the spreadsheet will immediately be recalculated. A printed report of the spreadsheet is often desirable. Ready your printer, then type the command /P which invokes the Print routine. You will then be prompted for several options. For this simple spreadsheet, just press ENTER for each of the options. The spreadsheet will print, and CALC will return to its original state, waiting for another command. 9 - A BRIEF TUTORIAL Finally, before exiting from CALC, let's save the spreadsheet on disk. Type /S to invoke the Save routine. You will be asked to enter the name of the spreadsheet; type TEST and press ENTER. CALC will say Saving file. Stand by . . . and the file will be written to your CALC disk. It can later be loaded using the /L command. (Note: If you get a message saying File Exists. Overwrite or Backup? , it only means that someone before you saved a file called TEST. Reply O to the message, and your TEST file will overwrite the one already on the disk.) This concludes the brief tutorial. The remainder of this manual is organized for reference. It is a good idea to read through it to familiarize yourself with some of the more sophisticated commands. But the best way to learn CALC is to use it. 10 - GENERAL INFORMATION ____________________________________________________________________ GENERAL INFORMATION The maximum size of a CALC spreadsheet is 64 columns by 256 rows. The maximum content of a cell is 74 characters, and the maximum column width is 75 characters. The amount of memory available for your spreadsheet is displayed at all times in the lower left corner of the screen. It is a number followed by the letter "K". If it says 16K, for example, you still have 16K of memory available for typing text and formulas. If it says 1K, be careful. Your spreadsheet is getting too big and may fill memory, causing the MEMORY FULL message. Three types of data may be entered into a cell: TEXT: Column headings, item descriptions and so forth, are text data. Normally you can just type the text and press ENTER, causing it to appear in a cell. However, if the text data is all numeric, or looks like a formula, enter a quotation mark or apostrophe to tell CALC that it's text. For example, in the tutorial above, when we entered the year 1984, we entered it as "1984 . If we had entered it without the quotation mark, CALC would have processed it as a value and displayed it as 1,984.00 . Any character may be typed in a text entry. If the text data is wider than the column, and there is nothing in the cell to its right, it will overlap into subsequent cells. If there is something in the cell to its right, the text data will be truncated on the screen. The entire text entry is retained in the cell, even if it does not all display. Text can be left- or right-justified in the column by using the Format (/F) command. Text entries can be from 1 to 74 characters in length. 11 - GENERAL INFORMATION VALUE: A value is a number. When a value is entered into a cell, it is displayed according to current defaults. For example, you enter 12345 and it is displayed as 12,345.00 . Each value is displayed according to the display attributes of the cell. These attributes can be changed using the Format (/F) command. CALC will accept values of any size. However, values which have more than 14 decimal places to the left or right of the decimal will appear in scientific notation. FORMULA: A formula is one or more cell coordinates and/or one or more values, combined using arithmetic operators. For example, a formula might simply add together several numbers: A1 + B1 + C1 + D1 or it might perform a more complex task: A1*2.5+(SUM(A1:D1)-.00005) Formulas can be up to 74 characters in length. A formula can have any combination of cell coordinates, values, math functions, statistical functions and IF functions, each separated by an operator. An operator is a symbol or word which combines two numbers mathematically. For example, the plus sign is the operator for adding numbers together. CALC has many operators that can be used in formulas. They are: Operator Purpose Example + Addition A1 + 12345 - Subtraction A1 - 44.6 * Multiplication A1 * 2 / Division A1 / .003322 ^ Exponentiation 2 ^ 8 % Percentage A1 % A5 AND Logical And (complement) A1 AND A2 OR Logical Or (conjunction) A1 OR A2 NOT Logical Not (disjunction) NOT A5 \ Modulo (remainder) A1 \ 10 12 - GENERAL INFORMATION When CALC processes a formula, it follows standard mathematical rules of precedence: . Exponentiation (^) is done first . multiplication (*), division (/) and percentage (%) are next . then modulo (\) . then addition (+) and subtraction (-) . then relational operators (=, >, etc.) . and finally, logical operators (AND, OR, NOT) Some of these operators are well-known and require no further explanation. Others are less common, and will be recognized only by those with a mathematics background. The following paragraphs give further explanation of the less common operators. The Percentage operator (%) means "as a percent of". For example, the formula 3 % 5 can be read "3 as a percent of 5", and returns 60 (3 is 60% of 5). 3 % 5 is the same as 3 * 100 / 5 . The Modulo operator (\) does division, then returns the remainder instead of the quotient. For example, 10 \ 3 returns 1. Instead of the backslash character, the word MOD may also be used in formulas to designate modulo. MOD must have a space on the left and right. 10 MOD 3 is the same as 10 \ 3 . Relational operators are normally used in IF functions to compare two numbers. They are discussed in the Alphabetical Reference section of this manual, under IF function. However, relational operators may be used in formulas without IF, to return the truth value of a relation. Logical, or Boolean, operators (AND, OR, NOT) are also usually used in conjunction with the IF function, but can be used in any formula. When two numbers are compared with a logical operator, a non-zero number is considered true, while zero is false. After logically combining the two numbers, if the result is true, a 1 is returned. If false, a zero is returned. The symbols &, | and ~ can be used for AND, OR and NOT if preferred. For example, 5 & 1 is the same as 5 AND 1 , and returns a value of 1 (true). 13 - GENERAL INFORMATION Parts of a formula may be nested in parentheses, up to thirty levels. The following rules apply to formulas with parentheses: . Every left parenthesis must have a matching right parenthesis, or else a formula error occurs. . Parenthesized expressions are always evaluated from the inner-most to the outer-most. Using parentheses, the precedence of the arithmetic operators can be overridden. For example, A1+5*C1 will do the multiplication first, then the addition. (A1+5)*C1 will do the addition first. . Use parentheses to separate two operators in a row. For example, to multiply A1 times -5, the formula A1*-5 is not valid because the * and - are together. Instead use this format: A1*(-5) to accomplish the arithmetic. A formula may also include a function, or consist solely of a function. For specific information on each function, see the alphabetical reference section. Some spreadsheet users are accustomed to prefixing a function name with an @ sign. For example, instead of TAN(A1) they might prefer to enter @TAN(A1). To accomodate these users, CALC accepts @ signs anywhere in a formula, and ignores them. Some spreadsheet users are also accustomed to entering a period instead of a colon between cells in a range. For example, instead of SUM(A1:A20) they are accustomed to entering SUM(A1.A20) or maybe even @SUM(A1.A20). To accomodate these users, CALC accepts a period instead of a colon in a range, and ignores @ signs. Spaces may also be used freely in formulas. Lower-case characters may also be used, but they will be translated to upper-case (capital) letters. Spaces may not be used in the middle of function names or cell references. For example, AVE RAGE and A 12 are not acceptable in a formula. Values and formulas may contain numbers in exponential form (similar to scientific notation). The mantissa and exponent are 14 - GENERAL INFORMATION separated by the letter E (or e). A plus or minus sign is optional on the exponent. The E is effectively the same as *10^. Here are some examples of exponential numbers and formulas with exponential numbers: 3E10 5e-2 -5E+2 A1+3E10 -5E-2 + A1 - 3E10 15 - KEYBOARD CONVENTIONS ____________________________________________________________________ KEYBOARD CONVENTIONS Certain keys perform special functions in CALC. These keys fall into two categories; cell cursor keys and entry line keys. Once you start entering data, CALC goes into data entry mode, and only entry line keys will be recognized from the keyboard until the line is entered or cancelled. CELL CURSOR KEYS Special Key Alternate Purpose of key UP ARROW CONTROL-E Moves the cell cursor up one row. DOWN ARROW CONTROL-X Moves the cell cursor down one row. LEFT ARROW CONTROL-S Moves the cell cursor left one BACKSPACE column. BACKTAB RIGHT ARROW CONTROL-D Moves the cell cursor right one TAB column. HOME Moves the cell cursor to the upper left corner of the screen. END Moves the cell cursor to the lower right corner of the screen. PAGE UP CONTROL-R Moves the cell cursor back (up) one screen. (to center row) PAGE DOWN CONTROL-C Moves the cell cursor forward (down) one screen. (to center row) CONTROL-W Moves the screen down one row without moving the cell cursor. 16 - KEYBOARD CONVENTIONS CELL CURSOR KEYS Special Key Alternate Purpose of key CONTROL-Z Moves the screen up one row without moving the cell cursor. CONTROL-N Inserts a row above the row where the cell cursor is located. CONTROL-Y Deletes the row where the cell cursor is located. = CONTROL-Q Prompts for a cell, and jumps to it. ! Recalculates all the formulas in the spreadsheet. / Prompts for a command code. ENTRY LINE KEYS Special Key Alternate Purpose of key BACKSPACE CONTROL-S Moves the data cursor left one character. TAB CONTROL-D Moves the data cursor right one character. ESCAPE CONTROL-C Cancels the current command or entry, and erases the entry line. INSERT CONTROL-V Toggles on/off insert mode. DELETE CONTROL-G Deletes the character of input data at the cursor. 17 - KEYBOARD CONVENTIONS ENTRY LINE KEYS Special Key Alternate Purpose of key ENTER or COMMA Enters a command and processes it. RETURN CONTROL-T Erases input data from the cursor to the end of the entry line. UP ARROW Enters a command, then moves the cell cursor up one row. DOWN ARROW Enters a command, then moves the cell cursor down one row. LEFT ARROW Enters a command, then moves the cell cursor left one column. RIGHT ARROW Enters a command, then moves the cell cursor right one column. UP ARROW and DOWN ARROW move the cell cursor up and down a line at a time. When data or a command is being entered on the entry line, these keys operate like the ENTER key, followed by the arrow key. When the cell cursor reaches the top or bottom line, it stops moving. When these keys are held down, causing them to repeat, and they move off the current screen, CALC goes into a high-speed mode. The screen is not updated until the key is let up. However, the coordinate is displayed in the lower left corner of the screen. LEFT ARROW and RIGHT ARROW move the cell cursor to the left and right, one column at a time. If the column being moved into is not currently on the screen, the screen will be "scrolled" left or right to display it. When the cursor reaches the left or right edge of the spreadsheet (column A or column BL), it stops moving. When these keys are held down, causing them to repeat, CALC goes into high-speed mode, described in the previous paragraph. When moving the cell cursor around, BACKSPACE works exactly like the LEFT ARROW key and TAB works exactly like the RIGHT ARROW key. 18 - KEYBOARD CONVENTIONS When data is being entered on the entry line, the TAB key and BACKSPACE key serve a different purpose. Instead of moving the cell cursor, they move the small data cursor. BACKSPACE is non-destructive, so you can move back to a previous point in the entry line without erasing any of the right-most characters. Pressing BACKSPACE while on the first position of a command entry will cause the command to be terminated and erased. Pressing HOME moves the cursor to the cell in the upper left corner of the screen. Pressing END moves the cursor to the cell in the lower right corner of the screen. Pressing HOME twice moves the cursor to cell A1. Pressing END twice moves the cursor to the highest cell in the spreadsheet. If the cursor is already there, pressing it has no effect. The ESCAPE key, and its alternate, CONTROL-C, are popular keys for cancelling a command. These keys are only effective when something has been typed on the entry line. Pressing one of them will erase everything on the entry line, and move the cursor to the beginning of the line. If a command is cancelled with one of these keys, it is erased and has no effect. If text, a value or a formula are cancelled, the entry line is erased, and the contents of the current cell are unaltered. Pressing the PAGE DOWN key advances the display so that the new screen contains the bottom three rows from the previous screen at the top of the new screen. The cell cursor is positioned on the middle row of the new screen. PAGE UP works just like PAGE DOWN, except that it moves backward instead of forward. Pressing CONTROL-W causes the screen to shift up one row, and a new row to be displayed at the bottom. Use it when you want to see some data that is just beyond the bottom of the screen. Note that CONTROL-W does not usually cause the cell cursor to move. The one exception is when the cell cursor is on the top line. Since the top line is being shifted off the screen, the cell cursor must move down one line to remain on the screen. CONTROL-Z works essentially the opposite of CONTROL-W: it causes the screen to shift down one row, and a new row to be displayed at the top. 19 - KEYBOARD CONVENTIONS CONTROL-N is a quick method to insert a row. Using this key combination will cause a row to be inserted above the row the cell cursor is on. The same result may be accomplished with the Insert (/I) command (see Insert command in the Alphabetical Reference). CONTROL-Y is a quick method to delete a row. Using this key combination causes the row at the cell cursor location to be deleted. The same result may be accomplished with the Delete (/D) command (see Delete command in the Alphabetical Reference section for details). The INSERT key is used to add characters in the middle of the entry line such as a formula or text. Use the tab key to move the data cursor to the spot where the insertion is to be done. Press the INSERT key to toggle on the insert mode. The data cursor becomes enlarged, indicating that insert mode is toggled on. Now type the characters to be inserted. They will be inserted at the cursor position, and the data from the cursor to the end of the entry will be shifted right. When data being shifted reaches the right edge of the entry line, it disappears and is dropped. The INSERT key is only effective when there is data in the entry line. Other editing keys, such as BACKSPACE and DELETE can be used freely while Insert Mode is toggled on. To toggle off Insert Mode, press INSERT a second time. Pressing ENTER also automatically turns off Insert Mode. Note: There is a configuration option to make the INSERT key insert a single blank when it is pressed. See the section on Customizing CALC. The DELETE key is only effective when entering data on the entry line. It causes the character at the data cursor to be erased, and all the characters to the right of it to be shifted left. To erase several characters, press DELETE multiple times. CONTROL-T is only effective when entering data on the entry line. It causes the character at the cursor and all data to the right of the cursor to be erased. The ENTER key (RETURN key) is used to enter a line of data. COMMA (,) can also be used to enter data. The one exception for the comma is when entering text. Since the comma is a valid text 20 - KEYBOARD CONVENTIONS character, only the ENTER key or arrow keys terminate a text field. The EQUAL SIGN (=) invokes CALC's "Go To Coordinate" routine. To jump to a specific cell, type = followed by the cell coordinate. For example, =Z99 will move the cell cursor immediately to Z99. CONTROL-Q is the same as the equal sign, and can be followed by one of six control keys, to move the cursor around on the spreadsheet: CONTROL-Q CONTROL-R Move cell cursor to cell A1. (or HOME HOME) CONTROL-Q CONTROL-C Move cell cursor to highest cell (or END END) with data in it. (For example, if the spreadsheet extends from row 1 to 20, and column A to K, this will go to cell K20.) CONTROL-Q CONTROL-E Move cell cursor to top row (or = UP ARROW) on screen (same column). CONTROL-Q CONTROL-X Move cell cursor to bottom row (or = DOWN ARROW) on screen (same column). CONTROL-Q CONTROL-S Move cell cursor to left-most (or = LEFT ARROW) column on screen (same row). CONTROL-Q CONTROL-D Move cell cursor to right-most (or = RIGHT-ARROW) column on screen (same row). Users of popular software programs will recognize these as common control key sequences for moving the cursor around the screen. The EXCLAMATION POINT (!) causes "recalculation"; i.e. all the formulas in the spreadsheet are re-computed. This command is normally used when automatic recalculation has been turned off. At startup, automatic recalculation is set on. Each time a number or formula is entered, the message "CALCULATING" appears, and the cursor ripples through all the formulas. As your spreadsheet gets larger, the pause required to recalculate may grow 21 - KEYBOARD CONVENTIONS annoyingly long. Turn off auto recalculation using the Global, Manual ( /GM ) command, then when all your data is entered, type ! to recalculate. As each cell is recalculated, the cursor "ripples" across the screen, showing its progress. Normally recalculation proceeds across rows, but it can be set to go column-by-column using the /Global, Column/Row command. The SLASH character (/) initiates a "command". You will be prompted with a list of valid commands such as Print, Format, etc. To invoke a command, key the first letter of the command. Most commands prompt for more information. Each command is described in detail in the "Alphabetical Reference" section. When typing a command or data, it is not necessary to wait for the prompt message to be displayed. If you know what to enter, just type it. CALC pauses briefly before displaying a message, anticipating that you might already know what it is going to ask. If you press a key while the message is displaying, you may only get part of the message. CALC stops immediately when a key is pressed, and processes that key. During operations that take several seconds, such as saving files, recalculating or replicating, it is best not to enter data; some of the keystrokes might be ignored. 22 - ALPHABETICAL REFERENCE ____________________________________________________________________ Alphabetical Reference Name Type Purpose ABS Function Absolute value ARRANGE Command Arrange (sort) columns or rows ATN Function Arctangent AVERAGE Function Average of a range of numbers BLANK Command Blank out one or more cells CONFIGURE Command Reconfigure screen, keyboard, printer COS Function Trigonometric Cosine COUNT Function Count of a range of numbers CPI Function Change characters per inch on printer DELETE Command Delete a row or column EDIT Command Edit the contents of a cell EXP Function Mathematical number e raised to the x power FIX Function Integer (ignores sign) FORMAT Command Change display characteristics of cell(s) GLOBAL Command Change various spreadsheet settings IF Function Conditional test; If...Then...Else INSERT Command Insert a row or column INT Function Integer (true integer; i.e. INT(-2.5) = -3) KEYWORD Function Display current date, time, page number LOAD Command Load a file into spreadsheet area from disk LOG Function Natural logarithm LOOKUP Function Table lookup based on key value LPI Function Change lines per inch on printer MAX Function Maximum value in a range of numbers MIN Function Minimum value in a range of numbers NOLF Function No line feed on this print line NOPRINT Function Don't print this line NPV Function Net present value of a range of numbers PAGE Function Page break or conditional page break on printer PAYMENT Function Compute payment, given principal, rate, periods PERIODS Function Compute periods, given principal, payment, rate PRINCIPAL Function Compute principal, given payment, rate, periods PRINT Command Print spreadsheet 23 - ALPHABETICAL REFERENCE Alphabetical Reference Name Type Purpose QUIT Command Exit from CALC RANDOM Function Random number RATE Function Compute rate, given principal, payment, periods REPLICATE Command Copy cells, and optionally adjust ROUND Function Round a number to specified decimals SAVE Command Save a spreadsheet onto disk SGN Function Sign of a number SIN Function Trigonometric sine function SQR Function Square root STDEV Function Compute standard deviation of a range SUM Function Sum a range of numbers TAN Function Trigonometric tangent TITLE Command Lock titles horizontally and/or vertically XTERNAL Command Retrieve data from other files or databases ZAP Command Clear the spreadsheet area 24 - ABS ____________________________________________________________________ ABS Function Purpose: Computes the absolute value of a cell or formula, and returns the value to the current cell or formula. Format: ABS(-35) Returns 35. ABS(A1) If A1 = 35, then ABS(A1) = 35. If A1 = 0, then ABS(A1) = 0. If A1 = -35, then ABS(A1) = 35. ABS(A1*2/B5) Resolves formula, then computes absolute value of the result. Remarks: ABS(x) can be entered into a cell, causing a negative number to be converted to a positive number, and used as the cell value. Or the ABS function can be used in a formula, and/or may have a formula as its argument. Example: A spreadsheet compares two months' expenses (In columns B and C), and prints a third column (D), called "variance". The variance column is used in some later computations which require all the numbers to be positive. In cell D1 we want the difference between B1 and C1, with the sign removed if it is negative. We enter: ABS(B1-C1) The Replicate command can then be used to copy the formula down the column. 25 - ARRANGE ____________________________________________________________________ ARRANGE Command (/A) Purpose: Sorts a range of rows or columns into ascending or descending sequence. Prompts: After entering /A you are prompted: Column sort or Row sort? Respond C to sort cells which run down a column, or R to sort cells which run across a row. The default is C, since most sorts are done on a column of names or numbers. The remaining prompts shown here refer to Column sorts. Row sorts have similar prompts. Entering C, the next prompt is: Column letter to be sorted (or range if partial column) To sort all the cells in a column, just enter the column letter, such as G, and press ENTER. To sort only a partial range in the column, enter the from and to cells separated by a colon. The third prompt is: Ascending or Descending? To sort the lines in normal (ascending) sequence, type A, or press ENTER. To sort them in reverse (descending) sequence, type D. The next prompt is: Second column letter to be sorted (press ENTER if none) If only one column is being sorted, press ENTER. If there is a second column to be sorted, enter its letter and CALC will ask if it is to be sorted ascending or 26 - ARRANGE descending. The next prompt is: Third column letter to be sorted (press ENTER if none) Press ENTER if there is no third column. If a letter is entered, CALC asks if it is to be sorted ascending or descending. CALC now sorts the specified lines into sequence, redisplays them, and adjusts formulas. Remarks: Minimal input is required to do most sorts. For example, to sort an entire spreadsheet in customer name sequence, if the names are in column B, type: /ACB (ENTER) (ENTER) (ENTER) If there are headings on the spreadsheet, you do not want them sorted with the detail lines. Enter a range of cells that excludes the headings: /AC B5:B80 (ENTER) (ENTER) (ENTER) The sort precedence used by CALC is as follows: 1. Empty cells 2. Text spaces 3. Text special characters ($, %, &, etc.) 4. Text lower/upper case letters (a, A, b, B, c, C, etc.) 5. Text numerals ("0", "1", "2", etc.) 6. Negative values/formulas (-3, -2, -1) 7. Zero values/formulas 8. Positive values/formulas (1, 2, 3) This precedence is not ASCII sequence; it is a popular collating sequence for reports. But what if your sort requires precise ASCII sequence? Or you need to ignore upper/lower case. Or ignore the sign of a number. Or sort numbers ahead of text. In those cases, the standard precedence of the /Arrange command can be changed with configuration options in the .PRO file. See the section 27 - ARRANGE titled Customizing CALC for more information. The precedence for descending sorts is essentially the same, except text is sorted z, Z, y, Y, x, X, etc., text numerals are sorted 9, 8, 7, etc. and positive values are sorted before zero, then negative values. Note that values and formulas are sorted by their numeric value, not by their contents. In other words, the number is sorted, not the formula text. Formatting options are also not included in the sort. For example, if a value has a floating dollar sign, the dollar sign is not part of the sorted data. If text is right-justified in the cell, leaving spaces on the left, the spaces are not part of the sort unless they were input as part of the text string. If two cells have the same value, the one which comes first remains first after sorting. Because of this, it is possible to sort on more than three keys, even though CALC allows for only a three key sort. Suppose you want to sort a spreadsheet on five columns: A through E, with A being the primary key, B the secondary, and so forth. To accomplish this, start by sorting column E, then sort column D, then C, then B, and finally A. At any point in the sort, the sequence of your data may look a bit strange, but when it is done, it will be in sequence by columns A, B, C, D, then E. If this is a sort you do regularly, you may want to set up a Smart Key to automatically go through all the steps. /Arrange can also be used to rearrange the columns of a spreadsheet. On an empty row, put a number in each column indicating where the column will appear in the new report. Then sort on that row. The columns will be rearranged in the new sequence, and formulas will be adjusted accordingly. One word of caution: /Arrange can scramble a spreadsheet into uselessness with just a few wrong keystrokes. It is 28 - ARRANGE advisable to /Save your spreadsheet before experimenting with a new sort. To exit from /A without sorting, press ESCAPE or BACKSPACE. Example: EXAMPLE #1: A list of students and their test scores has been entered into a spreadsheet. Student names (last name then first name) are in column A. Scores are in column B: A B 1 Smith, John 79 2 Jones, Sally 100 3 Doe, John 55 4 Doaks, Joe 79 To sort the spreadsheet alphabetically by student name, type /ACA (ENTER) (ENTER) (ENTER) . The revised spread- sheet looks like this: A B 1 Doaks, Joe 79 2 Doe, John 55 3 Jones, Sally 100 4 Smith, John 79 EXAMPLE #2: Using the same spreadsheet described in example 1, sort it by test score, with the highest scores at the top. If two or more students have the same score, they should appear alphabetically. Column B is the primary sort column, because we want to sort by test score first. Column A is the secondary sort column, because we want to sort any students with the same test scores alphabetically by name. Type: /ACB (ENTER) D A (ENTER) (ENTER) (ENTER) 29 - ARRANGE to sort on column B descending then column A ascending. After a pause, during which CALC loads the sort program and sorts the data, the spreadsheet is displayed: A B 1 Jones, Sally 100 2 Doaks, Joe 79 3 Smith, John 79 4 Doe, John 55 The students with the highest test scores appear first. Since Doaks and Smith both have the same score of 79, they appear alphabetically. If Jones' score had also been 79, she would have appeared alphabetically between Doaks and Smith. If the alphabetical sort in example 1 had been done just prior to this sort, it would not have been necessary to specify student names as a secondary sort field, since they would have already been in alphabetic sequence. 30 - ATN ____________________________________________________________________ ATN Function Purpose: Computes the arctangent of a cell or formula (in radians), and returns the value to the current cell or formula. Format: ATN(3) Returns 1.249045772 ATN(A1) If A1 = 3, and cell has 2 decimal places, returns 1.25 ATN(A1*2/B5) Resolves formula, then computes arctangent Remarks: ATN(x) can be entered into a cell, causing the arctangent of a number to be computed, and used as the cell value. Or the ATN function can be used in a formula, and/or may have a formula as its argument. Example: Set up a simple spreadsheet which allows a tangent value to be entered, and returns the arctangent in radians and degrees. Enter the following: A1: Tangent: A2: Radians: A3: Degrees: B1: 1 B2: ATN(B1) B3: B2*180/3.14159 When a value is typed into B1, the angle, in radians, displays in B2. The angle in degrees displays in B3. 31 - AVERAGE ____________________________________________________________________ AVERAGE Function Purpose: Computes the mean average of a range of numbers and returns the result to the current cell or formula. Format: AVERAGE(A1:A20) Averages a column of numbers AVERAGE(A5:E5) Averages a row of numbers AVERAGE(A1:D20) Averages a block of numbers Remarks: AVERAGE(m:n) can be entered into a cell, causing the specified range to be averaged, and used as the cell value. Or the AVERAGE function can be used in a formula. The AVERAGE function is actually a combination of two other functions: SUM and COUNT. AVERAGE(A1:A20) is the same as: SUM(A1:A20)/COUNT(A1:A20) The coordinate range specified in an AVERAGE function may be down a column, such as AVERAGE(A1:A20), it may be across a row, such as AVERAGE(A1:E1), or it may be a block of cells (designated by the upper-left and lower-right coordinates), such as AVERAGE(A1:D20). If the specified range of cells contains any empty cells, they are ignored both in sum and count. If the range contains any cells with text, ERROR is returned. Example: Column B has a string of numbers from B7 to B26 which are to be averaged. The result is to be placed in B27. At B27 enter: AVERAGE(B7:B26) After recalculation, B27 contains the mean average. 32 - BLANK ____________________________________________________________________ BLANK Command (/B) Purpose: Removes the contents from a cell or range of cells, leaving them blank. Prompts: After entering /B you are prompted for the cell or cell range. The default, if ENTER is pressed, is to blank the current cell (the one where the cell cursor is located). Remarks: The cell range may be a "block" of cells. For example the range A1:C2 blanks A1, B1, C1, A2, B2 and C2. The cell range may be the word ALL, in which case all the cells in the spreadsheet are blanked. The cell range may also be the word VALUES, in which case all the cells which contain values are blanked, but the cells with text and formulas remain intact. /B frees the memory the blanked cells are occupying. This can be useful if you have a large spreadsheet that has filled available memory. /B does not clear protected cells. A protected cell is one which has been marked by the /Format,Protect command as a cell that cannot be changed. To /Blank protected cells, they first must be unprotected using the /Format,Unprotect command. If a range of cells is /Blanked and some of them are protected, the protected ones are ignored (left unchanged). This is actually a handy feature in disguise. By selectively protecting cells on your spreadsheet, you can do a /Blank,ALL and have it clear only cells with unprotected data. For an example of this, see the NOPRINT function example. /Blank,VALUES clears only unprotected values, leaving protected values intact. 33 - BLANK To exit from /B without blanking any cells, press ESCAPE or BACKSPACE. Example: EXAMPLE #1: A number has accidentally been entered into cell D5, and it needs to be removed. If the cell cursor is located at D5, enter: /B (ENTER) If the cursor is not at D5, enter: /B D5 (ENTER) EXAMPLE #2: A spreadsheet is being modified to do a slightly different application. The last ten rows of the spreadsheet (rows 25 to 34) are to be deleted. Enter: /B A25:BL34 (ENTER) 34 - CONFIGURE ____________________________________________________________________ CONFIGURE Command (/C) Purpose: Configures the CALC display, keyboard and printer. The width, height, layout, and color of the screen display can be set, the spreadsheet matrix can be changed, and the printer can be selected. Configuration changes can be made temporarily or permanently. Prompts: Enter /C and you will be prompted: Width, Height, Layout, Color, Matrix, Keys, Printer, Save Enter the first letter of the desired option. The next prompt is one of the following: Width Enter screen width (40 to 132). Specify the number of characters across your screen. You can specify less characters than your screen actually has. However, if you specify more characters than your screen has, expect unpredictable results. If your computer has a color graphics board with both 40- character and 80-character modes, specifying 40 characters changes the screen to the larger "double wide" character set. Height Enter number of display lines on your screen (7 to 32). Specify the number of lines on your screen. You can specify less lines than your screen actually has. If you specify more lines than your screen has, expect unpredictable results. This parameter is especially useful for some of the smaller portable "laptop computers", which sometimes have less than 25 lines. 35 - CONFIGURE Layout Select screen layout: DME, DEM, MDE, MED, EDM, EMD. DME is an abbreviation for: Display/Message/Entry DEM is an abbreviation for: Display/Entry/Message MDE is an abbreviation for: Message/Display/Entry MED is an abbreviation for: Message/Entry/Display EDM is an abbreviation for: Entry/Display/Message EMD is an abbreviation for: Entry/Message/Display CALC comes configured as "DME"; i.e. the spreadsheet display area is at the top of the screen, the two message lines come below it, and the entry line is at the bottom. Several popular spreadsheet programs use this layout, but others use the "EMD" layout, with the entry line and message lines at the top. (Note: If you are new to CALC, it's best to use DME until you become familiar with it because this manual assumes a setting of DME.) Color Border, Display, Message, Entry, Cursor, Protected, 1Bargraph, 2Bargraph, 3Bargraph If your computer has a color board and a color monitor, this option allows you to reset the screen colors. Enter the first letter of one of the words above, and you are prompted: B to advance background color, F to advance foreground color. When you find the colors you want, press ENTER. As you press B or F, the colors change on the screen. Keep pressing the keys until you find the colors you like. There are eight background colors, and sixteen foreground colors. Press ENTER, to lock in the color for the current area, and you are prompted to select another area. Press BACKSPACE or ESCAPE to exit. Users with monochrome boards may want to experiment with this option too. By rotating "colors" on a monochrome board, inverse video, underlining and brightness can be changed. 36 - CONFIGURE It is possible to make text "disappear" by making the foreground and background color similar. If you have missing data after configuring colors, restore from your master copy of CALC, and start over. Matrix Enter the number of columns (1 to 255). CALC is configured for 64 columns by 256 lines. You can change the dimensions of the spreadsheet workarea, within certain limits. First of all, the total number of cells cannot exceed 16,384. So if you specify 256 columns wide, there will only be 64 lines in the matrix. On the other hand, if you specify only ten columns, the number of lines increase to 1,638. After you enter the number of columns, CALC prompts for the number of lines. The maximum number you may enter has been calculated, and is displayed on the entry line. If you want the maximum, just press ENTER. The matrix size is not saved with each spreadsheet. It is a startup option, which must be specified in a .PRO file, or must be /Configured each time. The Matrix option should only be used when the spreadsheet area is empty. If it is not empty, a warning message is given. You can either continue, and lose the current contents of the spreadsheet area, or ESCAPE, save the spreadsheet, then come back and reconfigure the matrix. Keys Which Smart Key to define? Smart keys allow a sequence of keystrokes to be assigned to a special key. When the key is pressed, the sequence is sent to CALC as if it had been typed by the operator. For example, if smart key 1 is set up as: /LEXAMPLE,,/GF 37 - CONFIGURE then whenever Alt-1 is pressed (Hold down Alt key and press the 1 key at the top of the keyboard), the string is sent to CALC. If CALC is awaiting a command at the time, it will load the file named EXAMPLE, then switch to Global Formula mode. Up to twelve smart keys can be set up on the smart key screen. They can later be invoked by holding down the ALT key and pressing one of the keys on the top row of the keyboard: 1-9, 0, - or =. Additional smart keys can be defined for other keys, such as the ten Function keys on the left, using the TRANSLATE x TO yyy option in the .PRO file. TRANSLATE is discussed in the section titled "Customizing CALC". Smart key definitions are not saved with the spreadsheet. To save them, use the /Configure,Save option and put them into a .PRO file. If they are in the .PRO file named CALC.PRO, they are automatically loaded when CALC is started. If they are in a different .PRO file, its name must be entered on the command line when starting CALC. .PRO files are discussed in detail in the section titled "Customizing CALC". Most of CALC's prompts can be terminated with a comma, so long strings of commands can usually be typed into a smart key without requiring special characters. However, if a special character such as a carriage return or backspace must be imbedded in a smart key string, it must be entered in a special format: square brackets enclosing the ASCII value; i.e. carriage return is [13] and backspace is [8]. In an earlier example we used commas to terminate the fields on /LEXAMPLE,, but to terminate the fields with carriage returns instead, the smart key definition is /LEXAMPLE[13][13] . The section titled "Customizing CALC" gives more information on smart key strings with imbedded special characters, and it includes a table of special keys and their equivalent ASCII values. 38 - CONFIGURE Printer Epson, Okidata, Toshiba, MPI. This prompt lists a few brands of printers. If your printer appears in the list, or if it is compatible with one in the list, enter the first letter of the printer name, and CALC will be custom configured for it. CALC works with almost any printer, so don't despair if yours isn't in the list. Only two things happen when you configure for a specific printer: 1. the CPI and LPI functions can be used to change characters per inch within a spreadsheet; and 2. when using the "setup" command while printing, you can press a single key to change character spacing, rather than type the printer control characters. Both of these capabilities can be achieved for any printer by using an only slightly more complex technique. So no features are really "lost" if your printer is not in the list; you just need to define your printer codes in a .PRO file. For more information, see the section titled Customizing CALC. Save Enter the name of the .PRO file. The current system options will be written to the file. Enter a file name. If the .PRO extension is left off, it will be added. All of the Configuration, Global and Print option settings are saved as they are currently configured. The various options are written to the .PRO file as keywords, which you can later change with a text editor if you want. Press ESCAPE to exit without saving. If a file named CALC.PRO exists, it is automatically 39 - CONFIGURE read when CALC is started. You can specify that a second .PRO file be loaded after CALC.PRO by typing the file name on the command line when starting CALC. For example, if you type CALC BW from DOS, CALC first loads CALC.PRO (if it exists), then loads BW.PRO. Remarks: If you are using CALC on an IBM PC with a standard 80-column color monitor, or on an IBM PC with a monochrome board and monitor, CALC does not need to be reconfigured unless you want to change the color or layout of the screen. If you have a computer or video monitor which requires you to type BW or SL on the command line at startup, you are actually telling CALC to read a profile file of the name BW.PRO or SL.PRO. To eliminate typing these codes each time, go to the DOS prompt and type: RENAME BW.PRO CALC.PRO and the options will be automatically loaded each time CALC is started. The "Slow" screen refresh must be specified on some brands of computers whose video RAM memory is not compatible with that of the IBM PC. If you try running CALC on your computer and it appears to load, but nothing displays on the screen, try the SL profile, which uses standard BIOS calls for video display. Some video boards cause excessive "flicker" on the screen in "fast" mode. If this flicker is annoying, specifying SL at startup will eliminate it. However, the trade-off is slower display of data on the screen. If the height of your display is less than 25 lines, perform the "Getting Started" procedure, then enter /C H followed by the number of lines on your display. 40 - CONFIGURE For more details on customizing and personalizing your version of CALC, see the section titled "Customizing CALC." 41 - COS ____________________________________________________________________ COS Function Purpose: Computes the trigonometric cosine of a cell or formula and returns the value to the current cell or formula. Format: COS(3.14159) Returns -1 COS(A1) If A1 = 3.14159, returns -1 COS(A1*2/B5) Resolves formula, then computes cosine Remarks: COS(x) can be entered into a cell, causing the cosine of a number to be computed, and used as the cell value. Or the COS function can be used in a formula, and/or may have a formula as its argument. Example: Set up a simple spreadsheet which allows a value in radians to be entered, and returns the cosine: A1: Radians: A2: Cosine: B1: 3.14159 B2: COS(B1) When a value is typed into B1, the cosine is displayed in B2. Enter 3.14159 in B1, and -1 is returned in B2. Now change the spreadsheet so degrees can be entered instead of radians: A1: Degrees: A2: Cosine: B1: 180 B2: COS(B1*3.14159/180) 42 - COUNT ____________________________________________________________________ COUNT Function Purpose: Counts the number of non-empty cells in a range of numbers and returns the resulting count to the current cell or formula. Format: COUNT(A1:A20) Counts a column of numbers COUNT(A1:E1) Counts a row of numbers COUNT(A1:D20) Counts a block of numbers Remarks: COUNT(m:n) can be entered into a cell, causing the specified range to be counted, and the count used as the cell value. Or the COUNT function can be used in a formula. The coordinate range specified in a COUNT function may be down a column, such as COUNT(A1:A20), it may be across a row, such as COUNT(A1:E1), or it may be a block of cells (designated by the upper-left and lower- right coordinates), such as COUNT(A1:D20). If the specified range of cells contains any empty cells, they are ignored by COUNT. If the range contains any cells with text, ERROR is returned. Example: Column B has a string of numbers from B7 to B26 which are to be counted. The result is to be placed in B27. At B27 enter: COUNT(B7:B26) After recalculation, B27 contains the count. 43 - CPI ____________________________________________________________________ CPI Function Purpose: Changes the character spacing on the printer to 10, 12 or 17 characters per inch. Format: CPI(10) Changes to 10 characters per inch CPI(12) Changes to 12 characters per inch CPI(17) Changes to 17 characters per inch Remarks: When CPI(n) is entered into a cell, it displays as [CPI(n)] on the screen. When the cell is printed, control characters are sent to the printer which change the character spacing, then the cell is printed as if it were empty. CPI can be an operand in an IF function, but cannot be used in a formula. If your printer is not an IBM, Epson, or Epson compatible, then CALC must be configured for your printer before using the CPI function. See the section titled "Customizing CALC" for more information. Some printers move the carriage to the left of the page when characters per inch are changed, so different character sizes cannot be printed on the same line. If your printer does this, it may take some experimenting with the CPI function and the NOLF (no line feed) functions to print two character widths on the same line. Example: Monthly sales detail is entered into CALC, and it is a wide report which requires compressed print to fit on one page. The last page, however, is a sales summary, and is to be printed ten characters per inch. In cell A1 (or anywhere above the first line) type CPI(17) (ENTER) and [CPI(17)] displays on the screen. Enter the monthly sales detail to be printed in compressed mode. 44 - CPI Let's say the sales detail ends on line 48. Go to A49 and enter PAGE then go to B49 and enter CPI(10) . Both of these functions appear on the screen enclosed in [square brackets]. The PAGE function causes a form feed to be sent to the printer, and the CPI(10) function resets the characters per inch back to ten. 45 - DELETE ____________________________________________________________________ DELETE Command (/D) Purpose: Deletes a row or column from the spreadsheet. Prompts: Enter /D and you are asked: Delete Row or Column? to which you must reply either R or C. The next prompt is either: Enter row number to be deleted. or Enter column letter to be deleted. The default, if ENTER is pressed, is to use the row number or column letter of the current cell (the one where the cell cursor is currently located). Remarks: If a column is deleted, all the columns to the right of it shift left. Likewise, if a row is deleted, all the rows below it shift up to fill the blank space. Any formulas referring to the shifted cells have their coordinates adjusted so they are still pointing at the same data. /D frees the memory the deleted cells are occupying. This can be useful if you have a large spreadsheet that has filled available memory. When a column is deleted, the column widths are adjusted to the left along with the data. To exit from /D without deleting any rows or columns, press ESCAPE or BACKSPACE. 46 - DELETE Another way to delete a row is to move the cell cursor to the row to be deleted and press CONTROL-Y. Example: A spreadsheet lists all customers and their aged accounts receivable. Row 22 has a former customer who can be deleted. To delete the row, either move the cursor to row 22 and enter: /D R (ENTER) or if you are not at row 22, enter: /D R 22 (ENTER) Row 22 disappears, and the data that was in row 23 moves to 22, 24 moves to 23, and so forth. 47 - EDIT ____________________________________________________________________ EDIT Command (/E) Purpose: Displays the contents of a cell on the entry line, so it does not have to be re-typed. Prompts: Enter /E and you will be prompted for the cell to be edited. The default, if ENTER is pressed, is to edit the contents of the current cell (the one where the cell cursor is currently located). Remarks: The contents of the specified cell are displayed on the entry line, and the cursor is positioned at the first character. By moving the cursor to the right, using the tab key, changes can be made to any character in the cell. When all the editing changes have been made, press ENTER to post the edited data to the new cell. Characters on the entry line can be deleted using the DELETE key. Insert mode can be toggled on and off using the INSERT key. To erase from the cursor to the end of the field, use the CONTROL-T key. When editing a formula, pressing the space bar erases from the cursor to end-of-line. To exit from /E without editing any cell, press ESCAPE or BACKSPACE. Example: A lengthy heading has been typed into cell A5. An identical heading needs to go into A40. Move the cursor to A40 and enter: /E A5 (ENTER) The heading appears on the entry line. Since it is to be 48 - EDIT copied identically, just press ENTER a second time to place the heading in A40. If changes had been necessary to the heading, the backspace or tab key could have been used to move through the entry, and corrections made as required. 49 - EXP ____________________________________________________________________ EXP Function Purpose: Computes the mathematical number e raised to a specified power. e is the base for natural logarithms. Returns the resulting value to the current cell or formula. Format: EXP(1) Returns e, which is 2.718282 EXP(A1) Returns e raised to the value in A1. EXP(A1*2/A5) Resolves the formula, then returns e raised to the resolved value. Remarks: EXP(x) can be entered into a cell, causing the specified computation, or the EXP function can be used in a formula. Example: Calculate e raised to the 4th power. Put the result in B27. At B27 enter: EXP(4) After recalculation, B27 contains e to the 4th power, or 54.59815. 50